USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'InsUpdCommunication')
	BEGIN
		DROP  Procedure  dbo.InsUpdCommunication
	END
GO

CREATE Procedure dbo.InsUpdCommunication (
	@ADDRESSGUID			VARCHAR(100)
,	@PARTNERGUID			VARCHAR(100)	
,	@COMMUNICATIONID		VARCHAR(10)		= '0'
,	@PHONE					VARCHAR(20)		= ''
,	@PHONEEXTENSION			VARCHAR(20)		= ''
,	@FAX					VARCHAR(20)		= ''
,	@CELLPHONE				VARCHAR(20)		= ''
,	@USERID					VARCHAR(10)		= '1'

)
AS
BEGIN
	
	IF EXISTS(SELECT 1 FROM TR_COMMUNICATION WHERE CommunicationID = CAST(@COMMUNICATIONID AS INT) )
	BEGIN		-- UPDATE
		UPDATE TR_COMMUNICATION
		SET	Phone			= @PHONE
		,	PhoneExtension	= @PHONEEXTENSION
		,	Fax				= @FAX
		,	CellPhone		= @CELLPHONE
		,	ChangedBy		= @USERID
		,	ChangeDate		= GETDATE()
		WHERE
			CommunicationId	= @COMMUNICATIONID
		AND	CAST(PartnerGuid AS VARCHAR(100)) = @PARTNERGUID
		AND	CAST(AddressGuid AS VARCHAR(100)) = @ADDRESSGUID
	END
	ELSE
	BEGIN		-- INSERT 
		DECLARE @newCommId		INT

		SELECT @newCommId	= ISNULL(MAX(CommunicationID), 0) +1 FROM TR_COMMUNICATION

		INSERT INTO TR_COMMUNICATION (CommunicationId,Phone,PhoneExtension,Fax,CellPhone,
								CreatedBy,CreateDate,ChangedBy,ChangeDate,PartnerGuid,AddressGuid)
		VALUES(
			@newCommId			--CommunicationId
		,	@PHONE				--Phone
		,	@PHONEEXTENSION		--PhoneExtension
		,	@FAX				--Fax
		,	@CELLPHONE			--CellPhone
		,	@USERID				--CreatedBy
		,	GETDATE()			--CreateDate
		,	@USERID				--ChangedBy
		,	GETDATE()			--ChangeDate
		,	@PARTNERGUID		--PartnerGuid
		,	@ADDRESSGUID		--AddressGuid	
		)
	END
END
GO
